在前幾篇文章有介紹 BigQuery UDF 用 macro 實作的方法，但這方法同樣也衍生了很多缺點，而這些缺點如果改用 model 來開發 UDF 就能解決。

dbt 本身並無法直接用 model 開發 UDF，但如果建立一個自定義的 materialization，就有機會使用 model 來開發了。

接下來幾篇文章將會介紹我們團隊是如何開發 UDF materialization，以及如何開發和使用 UDF。

建立 UDF materialization

因為 UDF 有兩種類型(function 和 table function)，因此會分成兩個 materialization 來開發，但兩者的 code 差異不大，所以以下範例都以 function 作為案例。

因為 UDF 有兩種類型(function 和 table function)，因此會分成兩個 materialization 來開發，但兩者的 code 差異不大，所以以下範例都以 function 作為案例。

1. 準備數據庫

   {% materialization function, adapter='bigquery' %}
   	-- 檢查 relation 是否已存在（如果存在且為其他類型報錯）
     {% set target_relation = this %}
     {% set existing_relation = load_cached_relation(this) %}
     {% if existing_relation is not none %}
       {{ exceptions.raise_compiler_error('Relation "' ~ target_relation ~ '" exists as ' ~ existing_relation.type) }}
     {% endif %}
   

   因為是建立 BigQuery 的 function，所以 adapter 指定為 bigquery。

   在準備數據庫的階段，先檢查關係( relation ) 是否存在，如果存在代表是非 function 的物件存在數據庫（因 function 是我們自創的類別，dbt 的 relation 沒有 function 類別）

   如果有非 function 的物件存在數據庫，就會拋出例外（避免 function 名稱與其他表、視圖同名）

2. 執行建立 function 的 SQL

   -- 取得 model description
   {% set model_description =  model.description %}
   -- 執行建立 UDF 語法
   {% call statement('main') -%}
     {{ get_create_function_as_sql(target_relation, sql, config, model_description) }}
   {%- endcall %}
   

   這段會先取得 model 的描述（寫在 schema.yml 中的 description），並呼叫 get_create_function_as_sql 這支 macro 來產生建立 function 的語法，後面會詳細說明 get_create_function_as_sql 的內容

其他部分與先前 materialization 的說明相似，因此就不多做說明，以下是完整的 materialization

# macros/udf/function.sql
{% materialization function, adapter='bigquery' %}
	-- 檢查 relation 是否已存在（如果存在且為其他類型報錯）
  {% set target_relation = this %}
  {% set existing_relation = load_cached_relation(this) %}
  {% if existing_relation is not none %}
    {{ exceptions.raise_compiler_error('Relation "' ~ target_relation ~ '" exists as ' ~ existing_relation.type) }}
  {% endif %}
  
  -- 執行 pre-hooks
  {{ run_hooks(pre_hooks) }}
  
  -- 取得 model description
  {% set model_description =  model.description %}
 
	-- 執行建立 UDF 語法
  {% call statement('main') -%}
    {{ get_create_function_as_sql(target_relation, sql, config, model_description) }}
  {%- endcall %}
 
  {{ adapter.commit() }}
 
  -- 執行 post-hooks
  {{ run_hooks(post_hooks) }}
  
  -- 更新 Relation cache：幫助 DBT 快速確認此 relation 存在
  {{ return({'relations': [target_relation]}) }}
 
{% endmaterialization %}

function SQL 語法

在建立 function 的階段，呼叫一支 get_create_function_as_sql macro 以在 BigQuery 中建立 function 物件，以下就來講解這支 macro 的內容

# macros/udf/get_create_function_as_sql.sql
{% macro get_create_function_as_sql(relation, sql, config, model_description) -%} 

  {%- set return_type = config.require('return_type') -%}

  {%- set params = config.require('params') -%}
  {%- set params_string -%}
    {%- for param in params -%}
      {{ param }}
      {%- if not loop.last -%}, {%- endif -%}
    {%- endfor -%}
  {%- endset -%}

  CREATE OR REPLACE FUNCTION {{ relation }} ({{ params_string }}) RETURNS {{ return_type }} 
  OPTIONS(description="""{{ model_description }}""") # 將寫在 schema.yml 的 description 寫入到 BQ
  AS (
    {{ sql }}
  );
{%- endmacro %}

首先先講解參數：

• relation：model 的名稱
• sql：寫在 model 中的 sql 語句
• config：model 的 config 設定
• model_description：在 materialization 中取得的 model 在 schema.yml 的描述

再來一步步拆解 get_create_function_as_sql

# 在 config 中有兩個必填參數：return_type 和 params，分別代表 function 回傳類型和 function 參數列表
{%- set return_type = config.require('return_type') -%}
{%- set params = config.require('params') -%}

config 的必填參數 params，填入的格式為 [”param1 type”, “param2 type”]，原本是使用字典格式，但在 jinja 中字典的順序不會按照填入時的排序，而 function 在使用時要依照參數的順序來填入對應參數，如果順序不定會導致使用 function 時出錯，因此 params 改用順序固定的 list 格式

# 將 params 列表中的參數用逗號分隔組成字串
{%- set params_string -%}
  {%- for param in params -%}
    {{ param }}
    {%- if not loop.last -%}, {%- endif -%}
  {%- endfor -%}
{%- endset -%}

# 將 relation 填入宣告 function 名稱位置
# params_string 填入宣告參數位置
# return_type 填入 function 回傳型態位置
# model_description 填入 function 描述位置
# sql 填入 function 執行的位置
CREATE OR REPLACE FUNCTION {{ relation }} ({{ params_string }}) RETURNS {{ return_type }} 
OPTIONS(description="""{{ model_description }}""") # 將寫在 schema.yml 的 description 寫入到 BQ
AS (
  {{ sql }}
);

以上就是完整建立 materialization 的方法，下篇將會介紹實作 UDF materialization 後，要如何在 model 中開發及使用。

參考

• dbt materialization source code